﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using CommunitySystemModel;
using System.Data.SqlClient;

namespace CommunitySystem.BO.SystemManager
{
    public class MenuManagerBO
    {
        #region 单例
        private static MenuManagerBO instance = null;
        private string strConnection;
        private MenuManagerBO()
        {
            strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["JinYang"].ConnectionString;
        }

        public static MenuManagerBO GetInstance()
        {
            if (instance == null)
                instance = new MenuManagerBO();
            return instance;
        }
        #endregion

        public ObservableCollection<MenuItem> GetMenuItem(string condition)
        {
            ObservableCollection<MenuItem> menus = new ObservableCollection<MenuItem>();
            string sql = "select [ID], [Title], IsNull([Target], '') as Target, [Index], [PID], icon, IsDefault from MenuItem ";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += "  where " + condition;
            }
            sql += " order by [index] ";
            SqlConnection conn = new SqlConnection(strConnection);
            try
            {
                conn.Open();
                SqlCommand cmand = new SqlCommand(sql, conn);

                SqlDataReader reader = cmand.ExecuteReader();
                while (reader.Read())
                {
                    MenuItem item = new MenuItem();
                    item.ID = reader.GetInt32(0);
                    item.Title = reader.GetString(1);
                    item.Target = reader.GetString(2);
                    item.Index = reader.GetInt32(3);
                    item.PID = reader.GetInt32(4);
                    item.icon = reader["icon"] as byte[];
                    item.IsDefault = reader.GetBoolean(6);
                    menus.Add(item);
                }
                return menus;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        public ObservableCollection<MenuItem> GetMenuItemByPID(int  pid)
        {
            ObservableCollection<MenuItem> menus = new ObservableCollection<MenuItem>();
            string sql = string.Format(@"with Menu as(
	select ID, Title, Target, [index], PID, icon, IsDefault from MenuItem where pid={0}
	union all 
	select mi.ID, mi.Title, mi.Target, mi.[index], mi.PID, mi.icon, mi.IsDefault from Menu m 
	inner join MenuItem mi on mi.PID = m.id
)
select ID, Title, Isnull(Target, '') as [Target], [index], PID, icon, IsDefault from Menu  order by [index]", pid);
            
           
            SqlConnection conn = new SqlConnection(strConnection);
            try
            {
                conn.Open();
                SqlCommand cmand = new SqlCommand(sql, conn);

                SqlDataReader reader = cmand.ExecuteReader();
                while (reader.Read())
                {
                    MenuItem item = new MenuItem();
                    item.ID = reader.GetInt32(0);
                    item.Title = reader.GetString(1);
                    item.Target = reader.GetString(2);
                    item.Index = reader.GetInt32(3);
                    item.PID = reader.GetInt32(4);
                    item.icon = reader["icon"] as byte[];
                    item.IsDefault = reader.GetBoolean(6);
                    menus.Add(item);
                }
                return menus;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        public void AddMenuItem(MenuItem item)
        {
            string sql = "insert into MenuItem(Title, Target, [Index], PID, [icon], IsDefault) values(@Title, @Target, @Index, @PID, @icon, @IsDefault)    select @@IDENTITY";
            List<SqlParameter> sqlparams = new List<SqlParameter>()
            {
                new SqlParameter(){ParameterName="@Title", Value = item.Title },
                new SqlParameter(){ParameterName="@Target", Value = string.IsNullOrEmpty(item.Target)? "":item.Target},
                new SqlParameter(){ParameterName="@Index", Value = item.Index},
                new SqlParameter(){ParameterName="@PID", Value = item.PID},
                new SqlParameter(){ParameterName="@IsDefault", Value= item.IsDefault},
               
            };
            SqlParameter p = new SqlParameter() { ParameterName = "@icon", Value = item.icon, SqlDbType = System.Data.SqlDbType.Image };
            if (item.icon == null)
                p.Value = DBNull.Value;
            sqlparams.Add(p);
            if (item.PID == 0 && item.IsDefault)//系统主页
            {
                SqlConnection conn = null;
                SqlTransaction transaction = AccessHelper.GetInstance().BeginTransaction(out conn);
                SqlCommand cmmd = new SqlCommand(sql, conn, transaction);
                cmmd.Parameters.AddRange(sqlparams.ToArray());
                SqlDataReader reader = cmmd.ExecuteReader();
                reader.Read();
                int id = int.Parse(reader[0].ToString());
                reader.Close();
                UpdateIsDefault(id, transaction, conn);
            }
            else
            {
                AccessHelper.GetInstance().ExecuteSql(sql, sqlparams.ToArray());
            }
        }

        public void UpdateMenuItem(MenuItem item)
        {
            string sql = "update  MenuItem set Title= @Title, Target=@Target, [Index]=@Index, PID=@PID, icon=@icon, IsDefault=@IsDefault where ID=@ID";
            List<SqlParameter> sqlparams = new List<SqlParameter>()
            {
                new SqlParameter(){ParameterName="@Title", Value = item.Title},
                new SqlParameter(){ParameterName="@Target", Value = item.Target},
                new SqlParameter(){ParameterName="@Index", Value = item.Index},
                new SqlParameter(){ParameterName="@PID", Value = item.PID},
                new SqlParameter(){ParameterName="@ID", Value = item.ID},
                new SqlParameter(){ParameterName="@IsDefault", Value= item.IsDefault}
                //new SqlParameter(){ParameterName="@icon", Value = item.icon},
            };
            SqlParameter p = new SqlParameter() { ParameterName = "@icon", Value = item.icon, SqlDbType = System.Data.SqlDbType.Image };
            if (item.icon == null)
                p.Value = DBNull.Value;
            sqlparams.Add(p);
            if (item.IsDefault && item.PID == 0)
            {
                SqlConnection conn = null;
                SqlTransaction transaction = AccessHelper.GetInstance().BeginTransaction(out conn);
                SqlCommand cmmd = new SqlCommand(sql, conn, transaction);
                cmmd.Parameters.AddRange(sqlparams.ToArray());
                cmmd.ExecuteNonQuery();
                UpdateIsDefault(item.ID, transaction, conn);
            }
            else
            {
                AccessHelper.GetInstance().ExecuteSql(sql, sqlparams.ToArray());
            }
        }

        public void DeleteMenuItem(int id)
        {
            string sql = "delete  MenuItem  where ID=@ID";
            List<SqlParameter> sqlparams = new List<SqlParameter>()
            {                
                new SqlParameter(){ParameterName="@ID", Value = id},
            };
            AccessHelper.GetInstance().ExecuteSql(sql, sqlparams.ToArray());
        }

        /// <summary>
        /// 修改默认主页
        /// </summary>
        /// <param name="id"></param>
        private void UpdateIsDefault(int id, SqlTransaction transaction, SqlConnection conn)
        {
            string sql = "update MenuItem set IsDefault =0 where PID=0 and ID <> " + id;
            new SqlCommand(sql, conn, transaction).ExecuteNonQuery();
            transaction.Commit();
            
            conn.Close();
            conn.Dispose();
            //AccessHelper.GetInstance().EndTransaction(transaction);
        }

        

        public ObservableCollection<MenuItem> GetSubMenuByUserID(int userid, int topid)
        {
            ObservableCollection<MenuItem> menus = new ObservableCollection<MenuItem>();
            string sql = string.Format(@"with Menu as(
	select  m.ID, m.Title, Isnull(m.[Target], '') as [Target], m.[Index], m.PID, m.icon, m.IsDefault from UserInfo u
                        left join Roles r on u.RoleID = r.ID
                        left join RoleMenu rm on rm.RoleID = r.ID
                        left join MenuItem m on m.ID = rm.MenuID and m.PID={1}
                        where u.UserID = {0} and m.ID is not null
	union all 
	select mi.ID, mi.Title, mi.Target, mi.[index], mi.PID, mi.icon, mi.IsDefault from Menu m 	
	inner join MenuItem mi on mi.PID = m.id
	inner join RoleMenu rm2 on rm2.MenuID = mi.ID
	inner join Roles r2 on r2.ID = rm2.RoleID
	inner join UserInfo u2 on u2.RoleID = r2.ID
	where u2.UserID ={0}
)
select ID, Title, Isnull(Target, '') as [Target], [index], PID, icon, IsDefault from Menu  order by [index]", userid, topid);
            SqlConnection conn = new SqlConnection(strConnection);
            try
            {
                conn.Open();
                SqlCommand cmand = new SqlCommand(sql, conn);

                SqlDataReader reader = cmand.ExecuteReader();
                while (reader.Read())
                {
                    MenuItem item = new MenuItem();
                    item.ID = reader.GetInt32(0);
                    item.Title = reader.GetString(1);
                    item.Target = reader.GetString(2);
                    item.Index = reader.GetInt32(3);
                    item.PID = reader.GetInt32(4);
                    item.icon = reader["icon"] as byte[];
                    item.IsDefault = reader.GetBoolean(6);
                    menus.Add(item);
                }
                return menus;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return null;
        }
    }
}
